package com.duleme.dao;

import com.duleme.common.DBUtils;
import com.duleme.entity.Book;
import com.duleme.entity.ShoppingCart;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ShoppingCartDao {


    public Boolean insertShoppingBook(int userID, Book book){
        String sql = "INSERT INTO shoppingcart(goodsId,goodsName,price,userId,totalPrice,count) VALUES(?,?,?,?,?,?);";
        try{
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,book.getBookId());
            statement.setString(2,book.getBookName());
            statement.setBigDecimal(3,new BigDecimal(book.getPrice()));
            statement.setInt(4,userID);
            statement.setBigDecimal(5,new BigDecimal(book.getPrice()));
            statement.setInt(6,0);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean checkBookExist(int userID,Book book){
        String sql = "SELECT * FROM shoppingcart WHERE userID=? AND goodsId = ?";
        try {
            Connection connection =DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,userID);
            statement.setInt(2,book.getBookId());
            ResultSet rs =statement.executeQuery();
            if (rs.next()){
                DBUtils.close(connection,statement,rs);
                return true;
            }
            return false;

        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean updateShopingBook(int userID,Book book,int bookCount){
        String sql = "UPDATE shoppingcart SET count = ? ,totalPrice = ? where userId = ? AND goodsId = ?;";
        try{
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,bookCount);
            BigDecimal totalPrice = new BigDecimal(book.getPrice()*bookCount);
            statement.setBigDecimal(2,totalPrice);
            statement.setInt(3,userID);
            statement.setInt(4,book.getBookId());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean addShopingBook(int userID,Book book,int bookCount){
        String sql = "UPDATE shoppingcart SET count = count + ? ,totalPrice = totalPrice+ ? where userId = ? AND goodsId = ?;";
        try{
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,bookCount);
            BigDecimal totalPrice = new BigDecimal(book.getPrice()*bookCount);
            statement.setBigDecimal(2,totalPrice);
            statement.setInt(3,userID);
            statement.setInt(4,book.getBookId());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }
        catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean deleteShoppingBook(int userID,Book book){
        String sql = "DELETE FROM shoppingcart WHERE goodsId=? AND userId = ?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,book.getBookId());
            statement.setInt(2,userID);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean deleteBuyBook(int userID,Book book,int bookCount){
        String sql = "UPDATE shoppingcart SET count = count - ? ,totalPrice = totalPrice - ? where userId = ? AND goodsId = ?;";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,bookCount);
            BigDecimal totalPrice = new BigDecimal(book.getPrice()*bookCount);
            statement.setBigDecimal(2,totalPrice);
            statement.setInt(3,userID);
            statement.setInt(4,book.getBookId());
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean deleteByUser(int userID){
        String sql = "DELETE FROM shoppingcart WHERE userId = ?";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,userID);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public Boolean deleteAll(){
        String sql = "DELETE FROM shoppingcart WHERE count = 0";
        try {
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            if (statement.executeUpdate()>0){
                DBUtils.close(connection,statement,null);
                return true;
            }
            return false;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public ShoppingCart getShoppingBook(int userID){
        String sql = "SELECT goodsId,count FROM shoppingcart WHERE userID=?";
        try {
            ShoppingCart shopC =new ShoppingCart();
            BookDao bookDao = new BookDao();
            Connection connection = DBUtils.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,userID);
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                int bookID = rs.getInt("goodsID");
                int count = rs.getInt("count");
                Book book = bookDao.findBookByBookId(bookID);
                shopC.setUserId(userID);
                for (int i=0;i<count;i++){
                    shopC.addBook(book);
                }
            }
            DBUtils.close(connection,statement,rs);
            return shopC;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args){
        /*BookDao bookDao= new BookDao();
        ShoppingCartDao shoppingCartDao = new ShoppingCartDao();
        Book book = bookDao.findBookByBookId(94);
//        Book book1 = bookDao.findBookByBookId(96);
//        shoppingCartDao.insertShoppingBook(100012,book);
//        shoppingCartDao.updateShopingBook(100012,book,12);
//        shoppingCartDao.insertShoppingBook(100012,book1);
//        shoppingCartDao.updateShopingBook(100012,book1,15);

        *//*ShoppingCart shoppingCart = shoppingCartDao.getShoppingBook(100012);
        System.out.println(shoppingCart.getCount());
        System.out.println(shoppingCart.getTotalPrice());*//*
        System.out.println(shoppingCartDao.addShopingBook(100020,book,1));*/
        new ShoppingCartDao().deleteAll();
    }
}
